import paramiko
import pymysql
from sshtunnel import SSHTunnelForwarder
import configparser
import datetime
import os
from prettytable import PrettyTable
from flask import Flask, render_template, request, jsonify
import json

class CDRQuerySystem:
    def __init__(self, config_file='config.ini'):
        """初始化系统，加载配置文件"""
        self.config = self.load_config(config_file)
        self.ssh_tunnel = None
        self.db_connection = None
        self.cursor = None

    def load_config(self, config_file):
        """加载配置文件"""
        if not os.path.exists(config_file):
            self.create_default_config(config_file)
        
        config = configparser.ConfigParser()
        # 关键修改：指定encoding='utf-8'读取配置文件
        config.read(config_file, encoding='utf-8')
        return config

    def create_default_config(self, config_file):
        """创建默认配置文件"""
        config = configparser.ConfigParser()
        
        # SSH配置
        config['SSH'] = {
            'host': 'your_ssh_host',
            'port': 22,
            'username': 'your_ssh_username',
            'password': 'your_ssh_password',
            'private_key': ''
        }
        
        # 数据库配置
        config['DATABASE'] = {
            'host': '127.0.0.1',
            'port': 3306,
            'database': 'cdr_db',
            'user': 'db_user',
            'password': 'db_password'
        }
        
        with open(config_file, 'w') as f:
            config.write(f)
        print(f"已创建默认配置文件 {config_file}，请修改后重新运行")
        exit(0)

    def create_ssh_tunnel(self):
        """创建SSH隧道"""
        try:
            ssh_config = self.config['SSH']
            
            private_key = None
            if ssh_config['private_key'] and os.path.exists(ssh_config['private_key']):
                private_key = paramiko.RSAKey.from_private_key_file(ssh_config['private_key'])
            
            self.ssh_tunnel = SSHTunnelForwarder(
                (ssh_config['host'], int(ssh_config['port'])),
                ssh_username=ssh_config['username'],
                ssh_password=ssh_config['password'] if not private_key else None,
                ssh_pkey=private_key,
                remote_bind_address=(self.config['DATABASE']['host'], int(self.config['DATABASE']['port']))
            )
            
            self.ssh_tunnel.start()
            print("SSH隧道连接成功")
            return True
        except Exception as e:
            print(f"SSH隧道连接失败: {str(e)}")
            return False

    def connect_database(self):
        """通过SSH隧道连接数据库"""
        try:
            db_config = self.config['DATABASE']
            
            self.db_connection = pymysql.connect(
                host='127.0.0.1',
                port=self.ssh_tunnel.local_bind_port,
                user=db_config['user'],
                password=db_config['password'],
                database=db_config['database'],
                charset='utf8'
            )
            
            self.cursor = self.db_connection.cursor(pymysql.cursors.DictCursor)
            print("数据库连接成功")
            return True
        except Exception as e:
            print(f"数据库连接失败: {str(e)}")
            return False

    def close_connections(self):
        """关闭数据库连接和SSH隧道"""
        if self.cursor:
            self.cursor.close()
        if self.db_connection:
            self.db_connection.close()
            print("数据库连接已关闭")
        if self.ssh_tunnel and self.ssh_tunnel.is_active:
            self.ssh_tunnel.stop()
            print("SSH隧道已关闭")

    # 按被叫号码查询
    def query_by_callee_pattern(self, pattern, date=None):
        """
        按被叫号码片段查询话单
        :param pattern: 被叫号码片段，支持多种查询模式
        :param date: 日期，格式'YYYYMMDD'，如'20250427'，None则查询所有分区表
        :return: 查询结果
        """
        try:
            # 处理表名，支持按日期分区的表
            if date:
                table_name = f"e_cdr_{date}"
                # 检查表是否存在
                self.cursor.execute("SHOW TABLES LIKE %s", (table_name,))
                if not self.cursor.fetchone():
                    print(f"错误：表 {table_name} 不存在！")
                    print("可能的原因：")
                    print("1. 日期格式错误")
                    print("2. 该日期的数据表不存在")
                    print("3. 数据库中没有该日期的通话记录")
                    
                    # 尝试查找相似的表
                    self.cursor.execute("SHOW TABLES LIKE 'e_cdr_%'")
                    existing_tables = self.cursor.fetchall()
                    if existing_tables:
                        print("\n可用的数据表：")
                        for table in existing_tables[:10]:  # 只显示前10个
                            table_name_str = list(table.values())[0]
                            print(f"  - {table_name_str}")
                        if len(existing_tables) > 10:
                            print(f"  ... 还有 {len(existing_tables) - 10} 个表")
                    
                    return None
            else:
                # 当不指定日期时，查询所有分区表
                print("未指定日期，查询所有分区表...")
                self.cursor.execute("SHOW TABLES LIKE 'e_cdr_%'")
                existing_tables = self.cursor.fetchall()
                if not existing_tables:
                    print("错误：没有找到任何分区表！")
                    return None
                
                print(f"找到 {len(existing_tables)} 个分区表")
                # 这里暂时只查询第一个表作为测试
                table_name = list(existing_tables[0].values())[0]
                print(f"测试查询表: {table_name}")
            
            # 构建查询语句，使用正确的列名并转换时间戳为北京时间
            available_columns = ['id', 'calleraccesse164', 'calleeaccesse164', 'starttime', 'stoptime', 'holdtime', 'customeraccount', 'customername', 'callergatewayid', 'calleegatewayid']
            selected_columns = ['id', 'calleraccesse164', 'calleeaccesse164', 'starttime', 'stoptime', 'holdtime', 'customeraccount', 'customername', 'callergatewayid', 'calleegatewayid']
            
            # 转换时间戳为北京时间
            select_clause = []
            for col in selected_columns:
                if col in ['starttime', 'stoptime']:
                    select_clause.append(f"FROM_UNIXTIME({col}/1000) AS {col}_beijing")
                else:
                    select_clause.append(col)
            
            # 自动生成查询条件：支持多种模式
            if pattern.startswith('*') and pattern.endswith('*'):
                # 模式: *147*5846 -> %147%5846%
                inner_pattern = pattern[1:-1]  # 去掉首尾的*
                search_pattern = f"%{inner_pattern}%"
                print(f"使用包含查询条件: {search_pattern}")
            elif pattern.startswith('*'):
                # 模式: *14708637862 -> %14708637862
                suffix = pattern[1:]  # 去掉开头的*
                search_pattern = f"%{suffix}"
                print(f"使用后缀查询条件: {search_pattern}")
            elif pattern.endswith('*'):
                # 模式: 147* -> 147%
                prefix = pattern[:-1]  # 去掉结尾的*
                search_pattern = f"{prefix}%"
                print(f"使用前缀查询条件: {search_pattern}")
            elif len(pattern) >= 7 and not '*' in pattern:
                # 模式: 1475846 -> 147%5846 (前三后四)
                prefix = pattern[:3]
                suffix = pattern[-4:]
                search_pattern = f"{prefix}%{suffix}"
                print(f"自动生成查询条件: {search_pattern} (前3位: {prefix}, 后4位: {suffix})")
            else:
                # 其他情况使用模糊查询
                search_pattern = f"%{pattern}%"
                print(f"使用模糊查询条件: {search_pattern}")
            
            # 先尝试查询被叫号码
            query = f"""
            SELECT {', '.join(select_clause)}
            FROM {table_name} 
            WHERE calleeaccesse164 LIKE %s
            ORDER BY starttime DESC
            LIMIT 5
            """
            
            print(f"执行SQL: {query}")
            print(f"查询参数: {search_pattern}")
            
            # 执行查询
            self.cursor.execute(query, (search_pattern,))
            results = self.cursor.fetchall()
            
            print(f"被叫查询结果: {len(results)} 条记录")
            if results:
                print("前几条记录:")
                for i, row in enumerate(results[:3]):
                    print(f"  记录{i+1}: 主叫={row.get('calleraccesse164', 'N/A')}, 被叫={row.get('calleeaccesse164', 'N/A')}")
            
            # 如果被叫查询没有结果，尝试查询主叫号码
            if not results:
                print("被叫查询无结果，尝试查询主叫号码...")
                query = f"""
                SELECT {', '.join(select_clause)}
                FROM {table_name} 
                WHERE calleraccesse164 LIKE %s
                ORDER BY starttime DESC
                LIMIT 5
                """
                
                print(f"执行主叫查询SQL: {query}")
                self.cursor.execute(query, (search_pattern,))
                results = self.cursor.fetchall()
                
                print(f"主叫查询结果: {len(results)} 条记录")
                if results:
                    print("前几条记录:")
                    for i, row in enumerate(results[:3]):
                        print(f"  记录{i+1}: 主叫={row.get('calleraccesse164', 'N/A')}, 被叫={row.get('calleeaccesse164', 'N/A')}")
            
            return results
        except Exception as e:
            print(f"查询失败: {str(e)}")
            return None

    # 按主叫号码查询
    def query_by_caller_pattern(self, pattern, date=None):
        """按主叫号码片段查询话单"""
        try:
            # 处理表名，支持按日期分区的表
            if date:
                table_name = f"e_cdr_{date}"
                self.cursor.execute("SHOW TABLES LIKE %s", (table_name,))
                if not self.cursor.fetchone():
                    print(f"错误：表 {table_name} 不存在！")
                    return None
            else:
                table_name = "e_cdr"
                self.cursor.execute("SHOW TABLES LIKE %s", (table_name,))
                if not self.cursor.fetchone():
                    print(f"错误：表 {table_name} 不存在！")
                    return None
            
            # 构建查询语句
            selected_columns = ['id', 'calleraccesse164', 'calleeaccesse164', 'starttime', 'stoptime', 'holdtime', 'customeraccount', 'customername', 'callergatewayid', 'calleegatewayid']
            
            # 转换时间戳为北京时间
            select_clause = []
            for col in selected_columns:
                if col in ['starttime', 'stoptime']:
                    select_clause.append(f"FROM_UNIXTIME({col}/1000) AS {col}_beijing")
                else:
                    select_clause.append(col)
            
            # 处理查询条件
            if pattern.startswith('*') and pattern.endswith('*'):
                search_pattern = f"%{pattern[1:-1]}%"
            elif pattern.startswith('*'):
                search_pattern = f"%{pattern[1:]}"
            elif pattern.endswith('*'):
                search_pattern = f"{pattern[:-1]}%"
            elif len(pattern) >= 7 and not '*' in pattern:
                prefix = pattern[:3]
                suffix = pattern[-4:]
                search_pattern = f"{prefix}%{suffix}"
            else:
                search_pattern = f"%{pattern}%"
            
            print(f"主叫查询条件: {search_pattern}")
            
            query = f"""
            SELECT {', '.join(select_clause)}
            FROM {table_name} 
            WHERE calleraccesse164 LIKE %s
            ORDER BY starttime DESC
            """
            
            self.cursor.execute(query, (search_pattern,))
            results = self.cursor.fetchall()
            return results
        except Exception as e:
            print(f"查询失败: {str(e)}")
            return None

    # 复合查询：同时指定主叫和被叫
    def query_by_caller_callee(self, caller_pattern, callee_pattern, date=None):
        """复合查询：同时指定主叫和被叫号码"""
        try:
            # 处理表名
            if date:
                table_name = f"e_cdr_{date}"
                self.cursor.execute("SHOW TABLES LIKE %s", (table_name,))
                if not self.cursor.fetchone():
                    print(f"错误：表 {table_name} 不存在！")
                    return None
            else:
                table_name = "e_cdr"
                self.cursor.execute("SHOW TABLES LIKE %s", (table_name,))
                if not self.cursor.fetchone():
                    print(f"错误：表 {table_name} 不存在！")
                    return None
            
            # 构建查询语句
            selected_columns = ['id', 'calleraccesse164', 'calleeaccesse164', 'starttime', 'stoptime', 'holdtime', 'customeraccount', 'customername', 'callergatewayid', 'calleegatewayid']
            
            # 转换时间戳为北京时间
            select_clause = []
            for col in selected_columns:
                if col in ['starttime', 'stoptime']:
                    select_clause.append(f"FROM_UNIXTIME({col}/1000) AS {col}_beijing")
                else:
                    select_clause.append(col)
            
            # 处理主叫号码查询条件
            if caller_pattern.startswith('*') and caller_pattern.endswith('*'):
                caller_search = f"%{caller_pattern[1:-1]}%"
            elif caller_pattern.startswith('*'):
                caller_search = f"%{caller_pattern[1:]}"
            elif caller_pattern.endswith('*'):
                caller_search = f"{caller_pattern[:-1]}%"
            elif len(caller_pattern) >= 7 and not '*' in caller_pattern:
                prefix = caller_pattern[:3]
                suffix = caller_pattern[-4:]
                caller_search = f"{prefix}%{suffix}"
            else:
                caller_search = f"%{caller_pattern}%"
            
            # 处理被叫号码查询条件
            if callee_pattern.startswith('*') and callee_pattern.endswith('*'):
                callee_search = f"%{callee_pattern[1:-1]}%"
            elif callee_pattern.startswith('*'):
                callee_search = f"%{callee_pattern[1:]}"
            elif callee_pattern.endswith('*'):
                callee_search = f"{callee_pattern[:-1]}%"
            elif len(callee_pattern) >= 7 and not '*' in callee_pattern:
                prefix = callee_pattern[:3]
                suffix = callee_pattern[-4:]
                callee_search = f"{prefix}%{suffix}"
            else:
                callee_search = f"%{callee_pattern}%"
            
            print(f"复合查询条件: 主叫={caller_search}, 被叫={callee_search}")
            
            query = f"""
            SELECT {', '.join(select_clause)}
            FROM {table_name} 
            WHERE calleraccesse164 LIKE %s AND calleeaccesse164 LIKE %s
            ORDER BY starttime DESC
            """
            
            self.cursor.execute(query, (caller_search, callee_search))
            results = self.cursor.fetchall()
            return results
        except Exception as e:
            print(f"查询失败: {str(e)}")
            return None

    def display_results(self, results):
        """格式化并显示查询结果"""
        if not results:
            print("没有找到匹配的话单记录")
            return
        
        if not results:
            print("没有找到匹配的话单记录")
            return
            
        # 动态获取列名并创建友好的显示名称
        columns = list(results[0].keys())
        display_names = []
        
        for col in columns:
            if col == 'id':
                display_names.append('ID')
            elif col == 'calleraccesse164':
                display_names.append('主叫号码')
            elif col == 'calleeaccesse164':
                display_names.append('被叫号码')
            elif col == 'starttime_beijing':
                display_names.append('开始时间(北京时间)')
            elif col == 'stoptime_beijing':
                display_names.append('结束时间(北京时间)')
            elif col == 'holdtime':
                display_names.append('通话时长(秒)')
            elif col == 'customeraccount':
                display_names.append('账户号码')
            elif col == 'customername':
                display_names.append('账户名称')
            elif col == 'callergatewayid':
                display_names.append('主叫网关')
            elif col == 'calleegatewayid':
                display_names.append('被叫网关')
            else:
                display_names.append(col)
        
        table = PrettyTable()
        table.field_names = display_names
        
        for row in results:
            table_row = []
            for col in columns:
                value = row.get(col, '')
                table_row.append(str(value))
            table.add_row(table_row)
            
        print(table)

    def run(self):
        """运行话单查询系统"""
        print("===== 话单查询系统 =====")
        
        # 建立连接
        if not self.create_ssh_tunnel():
            return
            
        if not self.connect_database():
            self.close_connections()
            return
        
        # 主循环
        try:
            while True:
                print("\n请选择操作:")
                print("1. 按被叫号码查询")
                print("2. 退出系统")
                
                choice = input("请输入选项 (1-2): ")
                
                if choice == '1':
                    # 按被叫号码查询
                    print("按被叫号码片段查询说明:")
                    print("支持的查询模式:")
                    print("- *147*5846  -> 包含147和5846的号码")
                    print("- *14708637862 -> 以14708637862结尾的号码")
                    print("- 147* -> 以147开头的号码")
                    print("- 1475846 -> 自动生成147%5846 (前三后四)")
                    print("- 其他 -> 模糊查询包含该片段的号码")
                    pattern = input("请输入被叫号码片段: ")
                    date_str = input("请输入查询的日期（格式YYYYMMDD，如20250727，直接回车则查询所有）: ")
                    
                    # 验证日期格式
                    if date_str.strip():
                        # 处理常见的日期格式错误
                        original_date = date_str
                        
                        # 如果输入了9位数字，可能是多了一个0，尝试去掉
                        if len(date_str) == 9 and date_str.isdigit():
                            if date_str.startswith('2025'):
                                date_str = date_str[:8]  # 取前8位
                                print(f"检测到日期格式错误，已自动修正: {original_date} -> {date_str}")
                        
                        # 验证修正后的日期格式
                        if len(date_str) != 8 or not date_str.isdigit():
                            print("日期格式错误！请输入8位数字，如：20250727")
                            print("常见错误：")
                            print("- 202507027 (多了一位) -> 应该是 20250727")
                            print("- 2025727 (少了一位) -> 应该是 20250727")
                            continue
                        
                        try:
                            # 验证日期是否有效
                            datetime.datetime.strptime(date_str, "%Y%m%d")
                        except ValueError:
                            print("无效的日期！请检查日期是否正确，如：20250727")
                            continue
                    
                    print(f"正在查询被叫号码包含 {pattern} 的记录...")
                    results = self.query_by_callee_pattern(pattern, date_str if date_str.strip() else None)
                    self.display_results(results)
                
                elif choice == '2':
                    print("感谢使用，再见！")
                    break
                
                else:
                    print("无效的选项，请重新输入")
        
        except KeyboardInterrupt:
            print("\n程序被用户中断")
        finally:
            self.close_connections()

if __name__ == "__main__":
    # 创建Flask应用
    app = Flask(__name__)
    
    # 全局CDR系统实例
    cdr_system = None
    
    @app.route('/')
    def index():
        """主页"""
        return render_template('index.html')
    
    @app.route('/test')
    def test():
        """测试页面"""
        return render_template('simple_test.html')
    
    @app.route('/api/batch_query', methods=['POST'])
    def api_batch_query():
        """批量查询API接口"""
        global cdr_system
        
        try:
            data = request.get_json()
            patterns = data.get('patterns', [])
            dates = data.get('dates', [])
            query_conditions = data.get('queryConditions', [])
            
            if not patterns:
                return jsonify({'error': '请输入查询条件'})
            
            # 确保dates列表长度与patterns一致
            while len(dates) < len(patterns):
                dates.append('')
            
            # 批量执行查询
            grouped_results = {}
            total_count = 0
            errors = []
            
            for i, pattern in enumerate(patterns):
                date_str = dates[i] if i < len(dates) else ''
                query_condition = query_conditions[i] if i < len(query_conditions) else {}
                
                print(f"调试信息: 处理查询 {i+1}")
                print(f"  pattern: {pattern}")
                print(f"  date_str: {date_str}")
                print(f"  query_condition: {query_condition}")
                
                # 验证日期格式
                if date_str.strip():
                    # 处理常见的日期格式错误
                    original_date = date_str
                    if len(date_str) == 9 and date_str.isdigit():
                        if date_str.startswith('2025'):
                            date_str = date_str[:8]
                    
                    if len(date_str) != 8 or not date_str.isdigit():
                        errors.append(f"{pattern}: 日期格式错误 - {original_date}")
                        continue
                    
                    try:
                        datetime.datetime.strptime(date_str, "%Y%m%d")
                    except ValueError:
                        errors.append(f"{pattern}: 无效的日期 - {date_str}")
                        continue
                
                try:
                    # 根据查询条件类型选择查询方法
                    print(f"执行查询: pattern={pattern}, date_str={date_str}")
                    print(f"查询条件: caller={query_condition.get('caller')}, callee={query_condition.get('callee')}")
                    
                    results = None
                    
                    # 判断查询类型
                    caller = query_condition.get('caller')
                    callee = query_condition.get('callee')
                    
                    if caller and callee:
                        # 复合查询：同时指定主叫和被叫
                        print(f"执行复合查询: 主叫={caller}, 被叫={callee}")
                        results = cdr_system.query_by_caller_callee(
                            caller, 
                            callee, 
                            date_str if date_str.strip() else None
                        )
                    elif caller:
                        # 主叫查询
                        print(f"执行主叫查询: {caller}")
                        results = cdr_system.query_by_caller_pattern(
                            caller, 
                            date_str if date_str.strip() else None
                        )
                    elif callee:
                        # 被叫查询
                        print(f"执行被叫查询: {callee}")
                        results = cdr_system.query_by_callee_pattern(
                            callee, 
                            date_str if date_str.strip() else None
                        )
                    else:
                        # 默认被叫查询（兼容旧格式）
                        print(f"执行默认被叫查询: {pattern}")
                        results = cdr_system.query_by_callee_pattern(
                            pattern, 
                            date_str if date_str.strip() else None
                        )
                    
                    if results is not None:
                        # 创建查询条件标识
                        if caller and callee:
                            condition_key = f"主叫:{caller} @ 被叫:{callee}"
                        elif caller:
                            condition_key = f"主叫:{caller}"
                        elif callee:
                            condition_key = f"被叫:{callee}"
                        else:
                            condition_key = pattern
                            
                        if date_str.strip():
                            condition_key += f" (日期: {date_str})"
                        else:
                            condition_key += " (所有日期)"
                        
                        # 转换结果为JSON格式
                        formatted_results = []
                        for row in results:
                            formatted_row = {}
                            for key, value in row.items():
                                if value is None:
                                    formatted_row[key] = ''
                                else:
                                    formatted_row[key] = str(value)
                            formatted_results.append(formatted_row)
                        
                        grouped_results[condition_key] = {
                            'pattern': pattern,
                            'date': date_str,
                            'caller': caller,
                            'callee': callee,
                            'data': formatted_results,
                            'count': len(formatted_results)
                        }
                        total_count += len(formatted_results)
                        print(f"查询成功: {condition_key}, 找到 {len(formatted_results)} 条记录")
                    else:
                        errors.append(f"{pattern}: 查询失败")
                        print(f"查询失败: {pattern}")
                except Exception as e:
                    errors.append(f"{pattern}: {str(e)}")
                    print(f"查询异常: {pattern} - {str(e)}")
            
            response_data = {
                'success': True,
                'grouped_results': grouped_results,
                'total_count': total_count,
                'patterns_queried': len(patterns)
            }
            
            if errors:
                response_data['errors'] = errors
            
            return jsonify(response_data)
            
        except Exception as e:
            return jsonify({'error': f'批量查询出错: {str(e)}'})
    
    @app.route('/api/query', methods=['POST'])
    def api_query():
        """API查询接口"""
        global cdr_system
        
        try:
            data = request.get_json()
            pattern = data.get('pattern', '')
            date_str = data.get('date', '')
            
            if not pattern:
                return jsonify({'error': '请输入被叫号码片段'})
            
            # 验证日期格式
            if date_str.strip():
                # 处理常见的日期格式错误
                original_date = date_str
                if len(date_str) == 9 and date_str.isdigit():
                    if date_str.startswith('2025'):
                        date_str = date_str[:8]
                
                if len(date_str) != 8 or not date_str.isdigit():
                    return jsonify({'error': '日期格式错误！请输入8位数字，如：20250727'})
                
                try:
                    datetime.datetime.strptime(date_str, "%Y%m%d")
                except ValueError:
                    return jsonify({'error': '无效的日期！请检查日期是否正确'})
            
            # 执行查询
            results = cdr_system.query_by_callee_pattern(pattern, date_str if date_str.strip() else None)
            
            if results is None:
                return jsonify({'error': '查询失败，请检查连接和参数'})
            
            # 转换结果为JSON格式
            formatted_results = []
            for row in results:
                formatted_row = {}
                for key, value in row.items():
                    if value is None:
                        formatted_row[key] = ''
                    else:
                        formatted_row[key] = str(value)
                formatted_results.append(formatted_row)
            
            return jsonify({
                'success': True,
                'data': formatted_results,
                'count': len(formatted_results)
            })
            
        except Exception as e:
            return jsonify({'error': f'查询出错: {str(e)}'})
    
    @app.route('/api/connect', methods=['POST'])
    def api_connect():
        """配置并连接数据库"""
        global cdr_system
        
        try:
            data = request.get_json()
            
            # 获取配置信息
            ssh_host = data.get('ssh_host', '')
            ssh_port = int(data.get('ssh_port', 22))
            ssh_username = data.get('ssh_username', '')
            ssh_password = data.get('ssh_password', '')
            db_host = data.get('db_host', '127.0.0.1')
            db_port = int(data.get('db_port', 3306))
            db_name = data.get('db_name', '')
            db_user = data.get('db_user', '')
            db_password = data.get('db_password', '')
            
            # 验证必要参数
            if not all([ssh_host, ssh_username, db_name, db_user]):
                return jsonify({'error': '请填写所有必要的配置信息'})
            
            # 创建新的CDR系统实例
            cdr_system = CDRQuerySystem()
            
            # 更新配置
            cdr_system.config['SSH']['host'] = ssh_host
            cdr_system.config['SSH']['port'] = str(ssh_port)
            cdr_system.config['SSH']['username'] = ssh_username
            cdr_system.config['SSH']['password'] = ssh_password
            cdr_system.config['DATABASE']['host'] = db_host
            cdr_system.config['DATABASE']['port'] = str(db_port)
            cdr_system.config['DATABASE']['database'] = db_name
            cdr_system.config['DATABASE']['user'] = db_user
            cdr_system.config['DATABASE']['password'] = db_password  # 允许为空
            
            # 建立SSH隧道
            if not cdr_system.create_ssh_tunnel():
                return jsonify({'error': 'SSH隧道连接失败，请检查SSH配置'})
            
            # 连接数据库
            if not cdr_system.connect_database():
                cdr_system.close_connections()
                return jsonify({'error': '数据库连接失败，请检查数据库配置'})
            
            return jsonify({
                'success': True,
                'message': '数据库连接成功！'
            })
            
        except Exception as e:
            if cdr_system:
                cdr_system.close_connections()
            return jsonify({'error': f'连接失败: {str(e)}'})
    
    @app.route('/api/status')
    def api_status():
        """检查系统状态"""
        global cdr_system
        if cdr_system and cdr_system.db_connection:
            return jsonify({'status': 'connected'})
        else:
            return jsonify({'status': 'disconnected'})
    
    # 启动系统
    print("正在启动话单查询系统...")
    cdr_system = None  # 初始化为None，等待前端配置
    
    print("系统启动成功！")
    print("请在浏览器中访问: http://localhost:5000")
    print("请先在前端界面配置SSH和数据库连接信息")
    
    # 启动Flask应用
    app.run(host='0.0.0.0', port=5000, debug=False)
    